package dao;

import bean.Advice;
import bean.PageBean;
import util.Connect;
import util.JdbcUtils;
import util.StringUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * The type Advice dao.
 *
 * @author yl
 */
public class AdviceDao {

    /**
     * 骑行用户一览
     *
     * @param pageBean         the page bean
     * @param advice           the advice
     * @param afterCreateTime  the after create time
     * @param beforeCreateTime the before create time
     * @param afterReplyTime   the after reply time
     * @param beforeReplyTime  the before reply time
     * @return list
     * @throws Exception the exception
     */
    public List<Object> adviceList(PageBean pageBean, Advice advice, String afterCreateTime, String beforeCreateTime, String afterReplyTime, String beforeReplyTime) throws Exception {
        StringBuilder sb = new StringBuilder("select adviseId adminId, creator, title, content, createTime, u.status as status, replyContent, replyTime, a.userName as userName from advise u inner join user a on u.creator=a.userId");
        List<Object> para = new ArrayList<>();
        if (StringUtil.isNotEmpty(advice.getAdviseId())) {
            sb.append(" and u.adviseId =?");
            para.add(advice.getAdviseId());
        }
        if (StringUtil.isNotEmpty(advice.getAdminId())) {
            sb.append(" and u.adminId =?");
            para.add(advice.getAdminId());
        }
        if (StringUtil.isNotEmpty(advice.getCreator())) {
            sb.append(" and u.creator =?");
            para.add(advice.getCreator());
        }
        if (StringUtil.isNotEmpty(advice.getTitle())) {
            sb.append(" and u.title like ?");
            para.add("%" + advice.getTitle() + "%");
        }
        if (StringUtil.isNotEmpty(advice.getContent())) {
            sb.append(" and u.content like ?");
            para.add("%" + advice.getContent() + "%");
        }

        if (StringUtil.isNotEmpty(afterCreateTime)) {
            sb.append(" and TO_SECONDS(u.createTime)>=TO_SECONDS(?)");
            para.add(afterCreateTime);
        }
        if (StringUtil.isNotEmpty(beforeCreateTime)) {
            sb.append(" and TO_SECONDS(u.createTime)<=TO_SECONDS(?)");
            para.add(beforeCreateTime);
        }

        if (StringUtil.isNotEmpty(advice.getStatus())) {
            sb.append(" and u.status =?");
            para.add(advice.getStatus());
        }
        if (StringUtil.isNotEmpty(advice.getReplyContent())) {
            sb.append(" and u.replyContent like ?");
            para.add("%" + advice.getReplyContent() + "%");
        }

        if (StringUtil.isNotEmpty(afterReplyTime)) {
            sb.append(" and TO_SECONDS(u.replyTime)>=TO_SECONDS(?)");
            para.add(afterReplyTime);
        }
        if (StringUtil.isNotEmpty(beforeReplyTime)) {
            sb.append(" and TO_SECONDS(u.replyTime)<=TO_SECONDS(?)");
            para.add(beforeReplyTime);
        }

        if (pageBean != null) {
            sb.append(" limit ").append(pageBean.getStart()).append(",").append(pageBean.getRows());
        }
        String sql = sb.toString();
        System.out.println(sql);
        List<Object> list = JdbcUtils.queryForArrObject(sql, para, Advice.class);
        System.out.println(list);
        return list;
    }

    /**
     * 骑行用户删除
     *
     * @param delIds the del ids
     * @return int
     * @throws Exception the exception
     */
    public int adviceDelete(String delIds) throws Exception {
        String sql = "delete from advise where adviseId in(?)";
        List<Object> para = new ArrayList<>();
        para.add(delIds);
        return JdbcUtils.update(sql, para);
    }

    /**
     * 骑行用户添加
     *
     * @param advice the advice
     * @return boolean
     * @throws Exception the exception
     */
    public boolean adviceAdd(Advice advice) throws Exception {
        StringBuilder sb = new StringBuilder("insert into advise values(");
        List<Object> para = new ArrayList<>();
        if (StringUtil.isNotEmpty(advice.getAdviseId())) {
            sb.append(" ,?");
            para.add(advice.getAdviseId());
        } else if (advice.getAdviseId() == null) {
            sb.append(" ,null");
        }
        if (StringUtil.isNotEmpty(advice.getAdminId())) {
            sb.append(" ,?");
            para.add(advice.getAdminId());
        } else if (advice.getAdminId() == null) {
            sb.append(" ,null");
        }
        if (StringUtil.isNotEmpty(advice.getCreator())) {
            sb.append(" ,?");
            para.add(advice.getCreator());
        }
        if (StringUtil.isNotEmpty(advice.getTitle())) {
            sb.append(" ,?");
            para.add(advice.getTitle());
        }
        if (StringUtil.isNotEmpty(advice.getContent())) {
            sb.append(" ,?");
            para.add(advice.getContent());
        }
        if (StringUtil.isNotEmpty(advice.getCreateTime())) {
            sb.append(" ,?");
            para.add(advice.getCreateTime());
        }
        if (StringUtil.isNotEmpty(advice.getStatus())) {
            sb.append(" ,?");
            para.add(advice.getStatus());
        }
        if (StringUtil.isNotEmpty(advice.getReplyContent())) {
            sb.append(" ,?");
            para.add(advice.getReplyContent());
        } else if (advice.getReplyContent() == null) {
            sb.append(" ,null");
        }
        if (StringUtil.isNotEmpty(advice.getReplyTime())) {
            sb.append(" ,?");
            para.add(advice.getReplyTime());
        } else if (advice.getReplyTime() == null) {
            sb.append(" ,null");
        }

        sb.append(")");
        String sql = sb.toString().replaceFirst(",", " ");
        return 1 == JdbcUtils.update(sql, para);
    }

    /**
     * 骑行用户修改
     *
     * @param advice the advice
     * @return int
     * @throws Exception the exception
     */
    public int adviceModify(Advice advice) throws Exception {
        StringBuilder sb = new StringBuilder("update advise u ");
        List<Object> para = new ArrayList<>();
        if (StringUtil.isNotEmpty(advice.getAdminId())) {
            sb.append(" , u.adminId=?");
            para.add(advice.getAdminId());
        }
        if (StringUtil.isNotEmpty(advice.getCreator())) {
            sb.append(" , u.creator =?");
            para.add(advice.getCreator());
        }
        if (StringUtil.isNotEmpty(advice.getTitle())) {
            sb.append(" , u.title =?");
            para.add(advice.getTitle());
        }
        if (StringUtil.isNotEmpty(advice.getContent())) {
            sb.append(" , u.content =?");
            para.add(advice.getContent());
        }
        if (StringUtil.isNotEmpty(advice.getCreateTime())) {
            sb.append(" , u.createTime =?");
            para.add(advice.getCreateTime());
        }
        if (StringUtil.isNotEmpty(advice.getStatus())) {
            sb.append(" , u.status =?");
            para.add(advice.getStatus());
        }
        if (StringUtil.isNotEmpty(advice.getReplyContent())) {
            sb.append(" , u.replyContent=?");
            para.add(advice.getReplyContent());
        }
        if (StringUtil.isNotEmpty(advice.getReplyTime())) {
            sb.append(" , u.replyTime=?");
            para.add(advice.getReplyTime());
        }
        sb.append(" where u.adviseId=?");
        para.add(advice.getAdviseId());
        String sql = sb.toString().replaceFirst(",", "set");
        System.out.println(sql);
        return JdbcUtils.update(sql, para);
    }

    /**
     * Advice count int.
     *
     * @param advice           the advice
     * @param afterCreateTime  the after create time
     * @param beforeCreateTime the before create time
     * @param afterReplyTime   the after reply time
     * @param beforeReplyTime  the before reply time
     * @return the int
     * @throws Exception the exception
     */
    public int adviceCount(Advice advice, String afterCreateTime, String beforeCreateTime, String afterReplyTime, String beforeReplyTime) throws Exception {
        StringBuilder sb = new StringBuilder("select count(*) as total from advise u");

        List<Object> para = new ArrayList<>();
        if (StringUtil.isNotEmpty(advice.getAdviseId())) {
            sb.append(" and u.adviseId =? ");
            para.add(advice.getAdviseId());
        }
        if (StringUtil.isNotEmpty(advice.getAdminId())) {
            sb.append(" and u.adminId =? ");
            para.add(advice.getAdminId());
        }
        if (StringUtil.isNotEmpty(advice.getCreator())) {
            sb.append(" and u.creator =?");
            para.add(advice.getCreator());
        }
        if (StringUtil.isNotEmpty(advice.getTitle())) {
            sb.append(" and u.title like ?");
            para.add("%" + advice.getTitle() + "%");
        }
        if (StringUtil.isNotEmpty(advice.getContent())) {
            sb.append(" and u.content like ?");
            para.add("%" + advice.getContent() + "%");
        }

        if (StringUtil.isNotEmpty(afterCreateTime)) {
            sb.append(" and TO_SECONDS(u.createTime)>=TO_SECONDS(?)");
            para.add(afterCreateTime);
        }
        if (StringUtil.isNotEmpty(beforeCreateTime)) {
            sb.append(" and TO_SECONDS(u.createTime)<=TO_SECONDS(?)");
            para.add(beforeCreateTime);
        }

        if (StringUtil.isNotEmpty(advice.getStatus())) {
            sb.append(" and u.status=?");
            para.add(advice.getStatus());
        }
        if (StringUtil.isNotEmpty(advice.getReplyContent())) {
            sb.append(" and u.replyContent like ?");
            para.add("%" + advice.getReplyContent() + "%");
        }

        if (StringUtil.isNotEmpty(afterReplyTime)) {
            sb.append(" and TO_SECONDS(u.replyTime)>=TO_SECONDS(?)");
            para.add(afterReplyTime);
        }
        if (StringUtil.isNotEmpty(beforeReplyTime)) {
            sb.append(" and TO_SECONDS(u.replyTime)<=TO_SECONDS(?)");
            para.add(beforeReplyTime);
        }

        String sql = sb.toString().replaceFirst("and", "where");
        List<Map<String, String>> result = JdbcUtils.queryForMap(sql, para);

        if (!result.isEmpty()) {
            return Integer.parseInt(result.get(0).get("total"));
        } else {
            return 0;
        }
    }

    /**
     * Find un process advice count int.
     *
     * @return the int
     * @throws SQLException the sql exception
     */
    public int findUnProcessAdviceCount() throws SQLException {
        String sql = "SELECT count(*) AS num FROM advise WHERE status = 0 OR status = 1";
        Connection connection = Connect.getInstance().getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet set = preparedStatement.executeQuery();
        int r = 0;
        if (set.next()) {
            r = set.getInt("num");
        }
        Connect.getInstance().freeConnection(connection);
        Connect.getInstance().freeStatement(preparedStatement);
        Connect.getInstance().freeResultSet(set);
        return r;
    }

    /**
     * Find un process advice list.
     *
     * @param page the page
     * @return the list
     */
    public List<Object> findUnProcessAdvice(PageBean page) {
        StringBuilder sqlBuilder = new StringBuilder("SELECT adviseId, adminId, creator, title, content, createTime, a.status, replyContent, replyTime, u.userName As userName FROM advise a INNER JOIN user u ON a.creator = u.userId WHERE a.status IN (0,1)");
        if (page != null) {
            sqlBuilder.append("limit ")
                .append(page.getStart())
                .append(",")
                .append(page.getRows());
        }
        return JdbcUtils.queryForArrObject(sqlBuilder.toString(), null, Advice.class);
    }
}
